Learning Objectives

After completing this lesson, you'll be able to:

• Set up a spreadsheet for use as a dynamic translation schema.
• Use the Schema (from Table) reader to read a schema from a spreadsheet.
• Use the Schema From Schema Feature schema source to use schema from a schema feature.

Resources

• Starting workspace
  • If your computer has FMEData, the file path is C:\FMEData\Workspaces\AdvancedReadingAndWriting\exercise-define-schema-from-a-schema-table-or-schema-feature.fmw
• Completed workspace
  • C:\FMEData\Workspaces\AdvancedReadingAndWriting\exercise-define-schema-from-a-schema-table-or-schema-feature-complete.fmw
• CommunityMapSchema.xlsx
  • C:\FMEData\Resources\DesktopAdvanced\CommunityMapSchema.xlsx
• CommunityMapAdvancedSchema.xlsx
  • C:\FMEData\Resources\DesktopAdvanced\CommunityMapSchema.xlsx
• Zoning.zip
  • C:\FMEData\Data\Zoning\Zones.tab

Introduction

In the past exercise, you created a new community map dataset for the planning department using a dynamic schema. We defined only two tables at the time, but now another is required, and the planning department wants you to update the workspace.

Rather than making changes each time they add more datasets, you believe you can create an Excel spreadsheet containing the schema definition. That way, the planning team can edit it themselves and do the same for all future updates.

1) Inspect Spreadsheet

Open and examine the spreadsheet CommunityMapSchema.xlsx (or C:\FMEData\Resources\DesktopAdvanced\CommunityMapSchema.xlsx).

If you don't have Excel, open it in the FME Data Inspector and switch to Table View.

The table has schema definitions for Firehalls, Parks, and Zones feature types. You can see how each new table has a series of attributes defined for it (each attribute is a separate row in the spreadsheet), including attribute type and attribute order. Geometry type only needs to be defined once per table.

2) Start Workbench

Start FME Workbench (2022.1 or later). Open the workspace from the previous exercise or the starting workspace.

3) Delete CommunityMap Resource Reader

Because we are using a spreadsheet to define our output schemas, the CommunityMap Resource Reader is no longer needed. Locate it in the Navigator window, right-click it, and choose Delete.

When prompted, click Yes to confirm that FME will remove all references to this dataset.

4) Add Excel File as Reader Resource

Now select Readers > Add Reader as Resource. In the dialog that opens, choose:

Click the Parameters button (OK will be greyed-out until you do). This dialog is where we can define how the table maps to the required schema.

Check the reader parameters at the top. They should show the dataset is an Excel format file. Select Sheet1 under the Select Table parameter if it is not already selected:

The first row should get used as the field names. If this is not the case, then click the parameters button above and set the values correctly:

Next, select the appropriate fields to match the required parameters (for example, Feature Type = FeatureType):

Click OK to close the dialog and again to add the resource reader.

5) Set Dynamic Parameters

Now inspect the feature type parameters for the writer feature type.

Under the User Attributes tab, remove the LastUpdatedBy attribute, as we've added this to the spreadsheet definition for each type and no longer need it here.

In the Parameters tab, click the Schema Sources edit button. Uncheck FireHalls and check CommunityMapSchema [SCHEMA_FROM_TABLE]:

Accept the changes to these parameters.

6) Add Reader

If you noticed, the schema spreadsheet included an entry for the Zones dataset, so add a reader (not a Resource – we want the data this time) as follows:

Once added, connect its reader feature type to the dynamic writer feature type.

7) Save and Run Workspace

Save the workspace and then run it.

Inspect the output. Notice that FME has written all three feature types, and their attribute schema matches what we defined in the Excel spreadsheet, including the LastUpdatedBy field for each one.

However, we still have not instructed FME on how to link the new, externally defined schema and the existing one. For this reason, all of the new attributes defined in the external schema table have missing values in the output data. We'll examine how to address this issue using the SchemaMapper in the next exercise.

8) Use a FeatureReader and Schema Feature

Before we address the schema mapping issue, let's try using a FeatureReader and schema feature to define the output schema in a dynamic workspace.

We'll keep the existing objects in the workspace and use them in the next exercise. But let's add a new Creator and FeatureReader to show a different way to configure a dynamic workspace.

Connect the Creator to the FeatureReader and configure the FeatureReader like this:

Click OK and run your workspace. This section of your workspace should look like this:

If you inspect the output ports, you will notice two things:

1. The <Schema> output port contains two schema features. They have unexposed attributes that describe this schema.
2. The <Generic> output port exports all the features from the two source datasets. They do not have exposed attributes. If you inspect them, you'll find they contain the source schema attributes as unexposed attributes. They also have the unexposed attribute fme_feature_type, which correctly names their source table, Parks or Zones.

We can use these two sets of features to write our data using a generic writer.

9) Add and Configure Another Dynamic Writer

Use Writers > Add Writer to add a writer. Configure it like this:

Click OK to add the writer feature type. Move it next to your FeatureReader and connect it to the <Schema> and <Generic> ports.

Double-click the writer feature type. You will see that it is automatically configured to use the fme_feature_type attribute to define the destination feature type names. That is correct. However, it is currently using the readers as Schema Sources. Click the ellipsis next to Schema Sources and choose only Schema From Schema Feature.

Click OK twice to close the dialogs. 

10) Save and Rerun Entire Workspace

Rerun the entire workspace. Inspect the output of the <Dynamic> writer feature type you just configured. You will find both the Parks and Zoning layers written out to GML with their original schemas.